const Model = require('./index')

class StatisticModel extends Model {
  /**
   * 文章、友圈、用户、标签数量查询
   */
  async statisticCountall () {
    const sql = `select 
      count(distinct a.id) as article_count, 
      count(distinct u.id) as user_count, 
      count(distinct sc.id) as circle_count
      from article a, user u, share_circle sc`
    const tagsql = 'select count(distinct t.id) as tag_count  from tag t'
    try {
      const data = await this.db.query(sql)
      const tagdata = await this.db.query(tagsql)
      return {
        ...data[0],
        ...tagdata[0]
      }
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * 近12个月文章和友圈发布数量趋势
   */
  async publicContentTrend () {
    const sql = `SELECT all_months.month,
      COUNT(distinct article.id) AS article_count,
      COUNT(distinct share_circle.id) AS circle_count
        FROM (
          SELECT 
          DATE_FORMAT(ADDDATE(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL temp.seq MONTH), '%Y-%m') as month
          from 
            (
              SELECT 0 AS seq UNION ALL
              SELECT -1 UNION ALL
              SELECT -2 UNION ALL
              SELECT -3 UNION ALL
              SELECT -4 UNION ALL
              SELECT -5 UNION ALL
              SELECT -6 UNION ALL
              SELECT -7 UNION ALL
              SELECT -8 UNION ALL
              SELECT -9 UNION ALL
              SELECT -10 UNION ALL
              SELECT -11
            ) as temp
        ) all_months
      LEFT JOIN article ON DATE_FORMAT(article.createTime, '%Y-%m') = all_months.month
      LEFT JOIN share_circle ON DATE_FORMAT(share_circle.createTime, '%Y-%m') = all_months.month
      GROUP BY all_months.month
      ORDER BY all_months.month`
    try {
      const data = await this.db.query(sql)
      return data
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * 近30天站点接口访问量统计
   */
  async statisticVisitCounts () {
    const unionall = new Array(29).fill(null).map((_o, index) => (index + 1)).reduce((prev, cur) => {
      prev += ` SELECT ${cur} UNION ALL`
      return prev
    }, '')
    const sql = `SELECT 
      DATE_FORMAT(date_range.date, '%Y-%m-%d') AS visit_date,
      COUNT(distinct visit.id) AS visit_count
      FROM
        (
          SELECT 
            CURDATE() - INTERVAL 30 DAY + INTERVAL temp.num DAY AS date
          FROM
            (SELECT 0 AS num UNION ALL ${unionall} SELECT 30) AS temp
        ) AS date_range
      LEFT JOIN visit ON DATE(visit.date) = date_range.date
      WHERE date_range.date >= CURDATE() - INTERVAL 30 DAY
      GROUP BY date_range.date
      ORDER BY date_range.date ASC`
    try {
      const data = await this.db.query(sql)
      return data
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * 站点标签库前30个标签查询
   */
  async findHotTags () {
    const sql = 'select tag.tagName from tag limit 30'
    try {
      const data = await this.db.query(sql)
      return data
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * 热度浏览量前10的文章统计
   */
  async fingHotArticle () {
    const sql = `select @row_number := @row_number + 1 AS rankId, arc.id, arc.articleTitle, arc.createTime, arc.viewCounts, user.nickname 
      from article arc
      left join user on user.id = arc.userId , (SELECT @row_number := 0) AS t
      order by arc.viewCounts desc limit 10`
    try {
      const data = await this.db.query(sql)
      return data
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * 站点各个地区访问量查询
   */
  async visitCountFronCity () {
    const sql = 'select lonlat from visit where lonlat is not null group by lonlat'
    try {
      const data = await this.db.query(sql)
      return data
    } catch (error) {
      throw new Error(error)
    }
  }
}

module.exports = new StatisticModel()
